package com.bj58.data.emailreports.service.impl;

import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.bj58.data.emailreports.dao.SrcDbPool.DBType;
import com.bj58.data.emailreports.model.TopJianliReportDetail;
import com.bj58.data.emailreports.model.TopJianliReportTop;
import com.bj58.data.emailreports.model.TopJianliReportTotal;
import com.bj58.data.emailreports.service.TopJianliService;
import com.bj58.data.emailreports.utils.CommonDataDeal;
import com.bj58.data.emailreports.utils.SortUtil;

public class TopJianliServiceImpl implements TopJianliService {
	
	private CommonDataDeal cdd = new CommonDataDeal();
	
	@Override
	public List<TopJianliReportTotal> getTopJianliTotalData(String nowDate, String lastDate,String lastWeek) throws Exception{
	
		List<TopJianliReportTotal> finalList = new ArrayList<TopJianliReportTotal>();
		List<String> gbList =new ArrayList<String>();
		gbList.add("statDate");
		
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		List<String> dateList = new ArrayList<String>();
		dateList.add(nowDate);
		dateList.add(lastDate);
		dateList.add(lastWeek);
			
		// 总体SQL(置顶摊销,现金摊销,置顶消费,现金消费,在线客户数,购买客户数)
		List<String> cols1=new ArrayList<String>();
		cols1.add("statDate");		
		cols1.add("topAmortize");cols1.add("cashAmortize");cols1.add("topCus");
		
		for (String date : dateList) {
			String month = date.substring(0,date.lastIndexOf("-")).replace("-", "");
			sql.delete(0, sql.length()+1);
			
			sql.append(" select stat_date,sum(cost)/100 as'置顶摊销',sum(cash)/100 as'现金摊销', ");
			sql.append(" count(distinct user_id) as'在线客户数' ");	
			sql.append(" from t_zhiding_user_stat_new_"+month+" ");
			sql.append(" where stat_date = '"+date+"' and disp_cate1_id in (9225,13952)");
			sql.append(" group by stat_date ");
			sql.append(" order by stat_date ");
			sql.append(" :"+DBType.db_94_data_app.getValue());
		
			sqlMap.put(sql.toString(), cols1);
		}
		
		List<String> cols2=new ArrayList<String>();
		cols2.add("statDate");	
		cols2.add("topCost");cols2.add("cashCost");cols2.add("costCus");
	
		for (String date : dateList) {
			String month = date.substring(0,4);
			sql.delete(0, sql.length()+1);
		
			sql.append(" select stat_date,sum(consume)/100 as'置顶消费',sum(consume_cash)/100 as'现金消费', ");
			sql.append(" count(distinct if(consume>0,user_id,null)) as'购买客户数' ");	
			sql.append(" from t_zhiding_consume_"+month+"");
			sql.append(" where stat_date = '"+date+"' and belong_cate1_id in (2080,2085)");
			sql.append(" group by stat_date ");
			sql.append(" order by stat_date ");
			sql.append(" :"+DBType.db_94_data_app.getValue());
		
			sqlMap.put(sql.toString(), cols2);
		}
	
		finalList = cdd.AssembledDataBySqlMap(sqlMap, TopJianliReportTotal.class, gbList);
	
		return finalList;
	}
	
	@Override
	public List<TopJianliReportDetail> getTopJianliDetailData(String nowDate, String lastDate,String lastWeek) throws Exception{
	
		List<TopJianliReportDetail> finalList = new ArrayList<TopJianliReportDetail>();
		List<String> gbList =new ArrayList<String>();
		gbList.add("statDate");
		
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		List<String> dateList = new ArrayList<String>();
		dateList.add(nowDate);
		dateList.add(lastDate);
		dateList.add(lastWeek);
		
		// 详细SQL 
		//摊销
		List<String> cols1=new ArrayList<String>();
		cols1.add("statDate");		
		cols1.add("topAmortize");cols1.add("cashAmortize");cols1.add("giftAmortize");
		//cols1.add("giftCardAmortize");
		cols1.add("topCus");cols1.add("ARPU1");
		
		for (String date : dateList) {
			
			String month = date.substring(0,date.lastIndexOf("-")).replace("-", "");
			sql.delete(0, sql.length()+1);
		
			sql.append(" select stat_date, sum(cost)/100 as'置顶摊销', sum(cash)/100 as'置顶现金摊销', sum(gift)/100 as'置顶优惠摊销', ");
			//sql.append(" sum(coupon)/100 as'置顶优惠券摊销', ");	
			sql.append(" count(distinct user_id) as'在线客户数', ");	
			sql.append(" sum(cost)/100/count(distinct user_id) as'ARPU1' ");	
			sql.append(" from t_zhiding_user_stat_new_"+month+"");
			sql.append(" where stat_date ='"+date+"' and disp_cate1_id in (9225,13952)");
			sql.append(" group by stat_date ");
			sql.append(" order by stat_date ");
			sql.append(" :"+DBType.db_94_data_app.getValue());
		
			sqlMap.put(sql.toString(), cols1);
		}

		//消费
		List<String> cols2=new ArrayList<String>();
		cols2.add("statDate");	
		cols2.add("topCost");cols2.add("cashCost");cols2.add("giftCost");
		//cols2.add("giftCardCost");
		cols2.add("costCus");cols2.add("ARPU2");cols2.add("topHourPrice"); 
	
		for (String date : dateList) {
			
			String month = date.substring(0,4);
			sql.delete(0, sql.length()+1);
		
			sql.append(" select stat_date,sum(consume)/100 as'置顶消费', sum(consume_cash)/100 as'现金消费', ");
			sql.append(" sum(consume_gift)/100 as'优惠消费', ");
			//sql.append("  sum(consume_coupon)/100 as'置顶优惠券消费', ");
			sql.append(" count(distinct if(consume>0,user_id,null)) as'购买客户数', ");	
			sql.append(" sum(consume)/100/count(distinct if(consume>0,user_id,null)) as'ARPU2', ");
			sql.append(" sum(consume)/100/sum(top_hour) as'置顶小时单价' ");	
			sql.append(" from t_zhiding_consume_"+month+"");
			sql.append(" where stat_date ='"+date+"' and belong_cate1_id in (2080,2085)");
			sql.append(" group by stat_date ");
			sql.append(" order by stat_date ");
			sql.append(" :"+DBType.db_94_data_app.getValue());
		
			sqlMap.put(sql.toString(), cols2);
		}
		
		//置顶展现条数
		List<String> cols3=new ArrayList<String>();
		cols3.add("statDate");cols3.add("topNum");
		
		for (String date : dateList) {
			
			String month = date.substring(0,date.lastIndexOf("-")).replace("-", "");
			sql.delete(0, sql.length()+1);
		
			sql.append(" select stat_date,");
			sql.append(" count(distinct entity_id)");	
			sql.append(" from m_zhiding_ad");
			sql.append(" where stat_date ='"+date+"' and disp_cate1_id in (9225,13952)");
			sql.append(" group by stat_date");
		//	sql.append(" order by stat_date ");
			sql.append(" :"+DBType.hive_data_dw.getValue());
		
			sqlMap.put(sql.toString(), cols3);
		}
		
		//广告
		List<String> cols4=new ArrayList<String>();
		cols4.add("statDate");	
		cols4.add("pv");cols4.add("adpv");cols4.add("disp");
		cols4.add("click");cols4.add("CPM");cols4.add("PVR");
		cols4.add("ASN"); cols4.add("CTR2"); 
	
		for (String date : dateList) {
			
			String month = date.substring(0,date.lastIndexOf("-")).replace("-", "");
			sql.delete(0, sql.length()+1);
		
			sql.append(" select stat_date, sum(pv) as'可出广告流量', sum(adpv) as'广告流量', ");
			sql.append(" sum(disp) as'广告展示量', sum(click) as'点击量', ");
			sql.append(" sum(cost)*10/sum(disp) as'CPM', sum(adpv)/sum(pv) as 'PVR' ,");
			sql.append(" sum(disp)/sum(adpv) as'ASN', sum(click)/sum(disp) as 'CTR2' ");
			sql.append(" from t_zhiding_city_cate_stat_new_"+month+"");
			sql.append(" where stat_date='"+date+"' and disp_cate1_id in(9225,13952)");
			sql.append(" group by stat_date ");
			sql.append(" order by stat_date ");
			sql.append(" :"+DBType.db_94_data_app.getValue());
		
			sqlMap.put(sql.toString(), cols4);
		}
		
		finalList = cdd.AssembledDataBySqlMap(sqlMap, TopJianliReportDetail.class, gbList);

		//计算ACP
		for(TopJianliReportDetail vo : finalList){
			vo.setACP((double)vo.getTopAmortize() / vo.getClick());
		}
		//为了PVR和CTR2显示百分比..
		for(TopJianliReportDetail vo : finalList){
			vo.setPVR(vo.getPVR()*100);
			vo.setCTR2(vo.getCTR2()*100);
		}		
		return finalList;
	}
	
	@Override
	public List<TopJianliReportTop> getTopJianliTopData(String nowDate) throws Exception{

		List<TopJianliReportTop> finalList = new ArrayList<TopJianliReportTop>();
		
		List<String> gbList =new ArrayList<String>();
		gbList.add("dispCate2");
	
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		// Top 10 cate2 SQL
		//摊销
		List<String> cols1=new ArrayList<String>();
		cols1.add("dispCate2");cols1.add("topAmortize");cols1.add("cashAmortize");cols1.add("topCus");
			
		String month = nowDate.substring(0,nowDate.lastIndexOf("-")).replace("-", "");
		sql.delete(0, sql.length()+1);
		
		sql.append(" select disp_cate2_name, sum(cost)/100 as t, sum(cash)/100 as'现金摊销', ");
		sql.append(" count(distinct user_id) as'在线客户数' ");	
		sql.append(" from t_zhiding_user_stat_new_"+month+"");
		sql.append(" where stat_date ='"+nowDate+"' and disp_cate1_id in (9225,13952)");
		sql.append(" and disp_cate2_name is not null ");
		sql.append(" group by disp_cate2_name ");
		sql.append(" order by t DESC ");
		sql.append(" limit 10 ");
		sql.append(" :"+DBType.db_94_data_app.getValue());
		
		sqlMap.put(sql.toString(), cols1);
			
		//消费 
		List<String> cols2=new ArrayList<String>();
		cols2.add("dispCate2");cols2.add("topCost");cols2.add("cashCost");cols2.add("costCus");
	
		String year = nowDate.substring(0,4);
		sql.delete(0, sql.length()+1);
			
		sql.append(" select belong_cate2_name, sum(consume)/100 as '置顶消费', sum(consume_cash)/100 as'现金消费', ");
		sql.append(" count(distinct if(consume>0,user_id,null)) as'购买客户数' ");	
		sql.append(" from t_zhiding_consume_"+year+"");
		sql.append(" where stat_date in('"+nowDate+"') and belong_cate1_id in (2080,2085)");
		sql.append(" and belong_cate2_name is not null ");
		sql.append(" group by belong_cate2_name ");
		sql.append(" :"+DBType.db_94_data_app.getValue());

		sqlMap.put(sql.toString(), cols2);
			
		finalList = cdd.AssembledDataBySqlMap(sqlMap, TopJianliReportTop.class, gbList);
		
		//排序
		List<TopJianliReportTop> finalList1 = new ArrayList<TopJianliReportTop>();

		SortUtil sortutil=new SortUtil();	
		finalList1 = sortutil.sortListByFileNameVo(finalList, TopJianliReportTop.class, "topAmortize" , 2);

		return finalList1;
	}
	
	@Override
	public List<TopJianliReportTop> getTopJianliTopCityData(String nowDate) throws Exception{
	
		List<TopJianliReportTop> finalList = new ArrayList<TopJianliReportTop>();
		
		List<String> gbList =new ArrayList<String>();
		gbList.add("dispCityName");
	
		Map<String,List<String>> sqlMap = new HashMap<String,List<String>>();
		StringBuffer sql = new StringBuffer();
		
		// Top 10 city SQL
		//摊销
		List<String> cols1=new ArrayList<String>();
		cols1.add("dispCityName");cols1.add("topAmortize");cols1.add("cashAmortize");cols1.add("topCus");
		
		String month = nowDate.substring(0,nowDate.lastIndexOf("-")).replace("-", "");
		sql.delete(0, sql.length()+1);
		
		sql.append(" select disp_city1_name, sum(cost)/100 as '置顶摊销', sum(cash)/100 as'现金摊销', ");
		sql.append(" count(distinct user_id) as'在线客户数' ");	
		sql.append(" from t_zhiding_user_stat_new_"+month+"");
		sql.append(" where stat_date in('"+nowDate+"') and disp_cate1_id in (9225,13952)");
		sql.append(" and disp_city1_name is not null ");
		sql.append(" group by 1 ");
		sql.append(" order by 2 DESC ");
		sql.append(" limit 10 ");
		sql.append(" :"+DBType.db_94_data_app.getValue());
		
		sqlMap.put(sql.toString(), cols1);
		
		//消费
		List<String> cols2=new ArrayList<String>();
		cols2.add("dispCityName");cols2.add("topCost");cols2.add("cashCost");cols2.add("costCus");
	
		String year = nowDate.substring(0,4);
		sql.delete(0, sql.length()+1);
			
		sql.append(" select belong_city1_name, sum(consume)/100 as '置顶消费', sum(consume_cash)/100 as'现金消费', ");
		sql.append(" count(distinct if(consume>0,user_id,null)) as'购买客户数'   ");	
		sql.append(" from t_zhiding_consume_"+year+"");
		sql.append(" where stat_date in('"+nowDate+"') and belong_cate1_id in (2080,2085)");
		sql.append(" and belong_city1_name is not null ");
		sql.append(" group by 1 ");
		sql.append(" :"+DBType.db_94_data_app.getValue());
		
		sqlMap.put(sql.toString(), cols2);
		
		finalList = cdd.AssembledDataBySqlMap(sqlMap, TopJianliReportTop.class, gbList);

		//排序
		List<TopJianliReportTop> finalList1 = new ArrayList<TopJianliReportTop>();

		SortUtil sortutil=new SortUtil();	
		finalList1 = sortutil.sortListByFileNameVo(finalList, TopJianliReportTop.class, "topAmortize" , 2);
		
		return finalList1;
	}	
}
